package domain.HQLQuery;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Restrictions;
import org.hibernate.service.ServiceRegistry;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class queryTest {
    public static void main(String[] args){
//        createSample();
//        findPeople();
//        findPeopleByHappendDate();
//        findPersonProperty();
//        findPersonAsMap();
//        testAggregateFunc();
//        testPloymorphic();
//        testNamedQuery();
//        testCriteria();
//        testSQLQuery();
//        testSecondCache();
        testQueryCache();
    }


    public static void findPeople(){
        Session session = HbmUtil.currentSession();
        Transaction transaction = session.beginTransaction();
        List p1 = session.createQuery("select distinct p from Person p join p.myEvents e where e.title = :eventTitle")
                .setParameter("eventTitle","warn")
                .list();
        for(Object e : p1){
            Person p = (Person) e;
            System.out.println(p.getName());
        }
        transaction.commit();
        HbmUtil.closeSession();
    }

    public static void findPeopleByHappendDate(){
        Session session = HbmUtil.currentSession();
        Transaction transaction = session.beginTransaction();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            Date start = sdf.parse("2017-07-05");
            List p1 = session.createQuery("select distinct p from Person p inner join p.myEvents e " +
                    "where e.happenDate between :firstDate and :endDate")
                    .setParameter("firstDate",start)
                    .setParameter("endDate",new Date())
                    .list();
            p1.forEach((o)->{
                Person p = (Person) o;
                System.out.println(p.getName());
            });
            transaction.commit();
            HbmUtil.closeSession();
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }

    //select有多项时，会封装成数组
    public static void findPersonProperty(){
        Session session = HbmUtil.currentSession();
        Transaction transaction = session.beginTransaction();
        List p1 = session.createQuery("select distinct p.id, p.name, p.age from Person p join p.myEvents").list();
        p1.forEach((o)->{
            Object[] objs = (Object[]) o;
            System.out.println(Arrays.toString(objs));
        });
        transaction.commit();
        HbmUtil.closeSession();
    }

    //将查询的结果封装成map对象
    public static void findPersonAsMap(){
        Session session = HbmUtil.currentSession();
        Transaction transaction = session.beginTransaction();
        List result = session.createQuery("select new map (p.name as PersonName) from Person as p").list();
        result.forEach((o)->{
            Map<String, String> map = (Map<String,String>) o;
            System.out.println(map.get("PersonName"));
        });
        transaction.commit();
        HbmUtil.closeSession();
    }

    //使用聚集函数
    public static void testAggregateFunc(){
       List result = getResult("select count(*), max(p.age) from Person as p");
       result.forEach((o)->{
           Object[] obj = (Object[]) o;
           System.out.println(Arrays.toString(obj));
       });
       HbmUtil.closeSession();
    }

    //多态查询
    public static void testPloymorphic(){
        List result = getResult("from java.lang.Object o");
        result.forEach(System.out::println);
    }

    //测试命名查询注解
    public static void testNamedQuery(){
        Session session = HbmUtil.currentSession();
        Transaction transaction = session.beginTransaction();
        List result = session.getNamedQuery("personNamedQuery").list();
        result.forEach(System.out::println);
    }

    //测试面向对象式查询
    public static void testCriteria(){
        Session session = HbmUtil.currentSession();
        List result = session.createCriteria(Person.class)
                .add(Restrictions.gt("age",11))
                .list();
        result.forEach(System.out::println);
    }

    //测试使用原生sql语句
    public static void testSQLQuery(){
        Session session = HbmUtil.currentSession();
        List result = session.createNativeQuery("select * from person_inf where age > ?1",Person.class)
                .setParameter("1","11")
                .list();
        result.forEach((o)->{
            System.out.println(o);
        });
    }

    //测试二级缓存
    public static void testSecondCache(){
        ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().configure().build();
        Configuration configuration = new Configuration().configure();
        SessionFactory sf = configuration.buildSessionFactory(serviceRegistry);
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        List list = session.createQuery("from Person as p").list();
        session.getTransaction().commit();
        System.out.println("-------------------------------");
        Session session1 = sf.getCurrentSession();
        session1.beginTransaction();
        Person person = session1.load(Person.class, 1);
        System.out.println(person);
        //通过统计API查看缓存的类信息
        Map cacheEntries = sf.getStatistics()
                .getSecondLevelCacheStatistics("domain.HQLQuery.Person")
                .getEntries();
        System.out.println(cacheEntries);
    }
    //测试查询缓存
    public static void testQueryCache(){
        Session session = HbmUtil.currentSession();
        session.beginTransaction();
        List result = session.createQuery("from Person as p")
                .setCacheable(true)
                .list();
        System.out.println(result);
        System.out.println("-------------------------------");
        session.getTransaction().commit();
        Session session1 = HbmUtil.currentSession();
        session1.beginTransaction();
        List result1 = session1.createQuery("from Person as p")
                .setCacheable(true)
                .list();
        System.out.println(result1);
        System.out.println("-------------------------------");
        long hitCount = HbmUtil.sessionFactory.getStatistics()
                .getQueryStatistics("from Person as p")
                .getCacheHitCount();
        System.out.println(HbmUtil.sessionFactory.getStatistics().getQueryStatistics("from Person as p"));
        System.out.println("查询缓存命中次数: "+ hitCount);
    }

    public static List getResult(String sql){
        Session session = HbmUtil.currentSession();
        Transaction transaction = session.beginTransaction();
        List result = session.createQuery(sql).list();
        return result;
    }

    public static void createSample(){
        Session session = HbmUtil.currentSession();
        Transaction transaction = session.beginTransaction();
        Person p1 = new Person("p1",10);
        Person p2 = new Person("p2",11);
        Person p3 = new Person("p3",12);
        MyEvent e1 = new MyEvent("info",new Date());
        MyEvent e2 = new MyEvent("info",new Date());
        MyEvent e3 = new MyEvent("warn",new Date());
        session.persist(e1);
        session.persist(e2);
        session.persist(e3);
        p1.getMyEvents().add(e1);
        p1.getMyEvents().add(e2);
        p2.getMyEvents().add(e2);
        p2.getMyEvents().add(e3);
        p3.getMyEvents().add(e3);
        p3.getMyEvents().add(e1);
        session.persist(p1);
        session.persist(p2);
        session.persist(p3);
        transaction.commit();
        HbmUtil.closeSession();
    }
}
